﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Data.SqlClient;
using System.Data;

namespace Dal
{
    public class DalService
    {
        SqlCommand cmd = null;
        string sql = "";
        public DataTable Nav_Inquire_All()
        {
            //一级导航全部信息
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                string sql = "select * from Navigation";

                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                return dt;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//一级导航全部信息

        public int Service_Count_File_Number(ModelPublic m)
        {
            //统计资料文件当天个数
            //InformationNumber
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            try
            {
                sql = "select count(*)as count from Service where NavId='" + m.NavId + "' and FileRoute is not null and DateDiff(dd,DateTime,'" + m.DateTime + "')=0";

                cmd = new SqlCommand(sql, conn);
                int num = int.Parse(cmd.ExecuteScalar().ToString());
                return num;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return 0;
        }//统计资料文件当天个数

        public bool Service_Add(ModelPublic m)
        {
            //服务指南添加
            SqlConnection conn = crecteSqlConnection.sqlconn();

            conn.Open();

            try
            {
                sql = "insert into Service values(" + m.NavId + "," + m.Release + "," + m.Type + "," + m.Adress + "," + m.Title + ","+m.Content+","+m.DateTime+",'" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "'," + m.Author + "," + m.Number + "," + m.FileName + "," + m.FileRoute + "," + m.Remarks + ",'false')";
                cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//服务指南添加

        public bool Service_IsTitle(ModelPublic m)
        {
            //判断是否存在相同标题

            SqlConnection conn = crecteSqlConnection.sqlconn();

            conn.Open();
            try
            {
                sql = "select Title from Service where Title='" + m.Title + "' and NavId='" + m.NavId + "' and Deleted='false'";
                cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteScalar().ToString() != null)
                    return true;
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//判断是否存在相同标题

        public DataTable Service_Inquire_All(ModelPublic m)
        {
            //获取全部信息(发布版)
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            DataTable dt = null;

            try
            {
                if (m.UserType == "0")
                    sql = "select Id,Type,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Service where NavId='" + m.NavId + "' and Deleted='false' and Release='True' order by DateTime desc";
                if (m.UserType == "1")
                    sql = "select Id,Type,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Service where NavId='" + m.NavId + "' and Deleted='false' and Release='False' and Remarks='stu' order by DateTime desc";


                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//获取全部信息(发布版)

        public DataTable Service_Inquire_All_Unpublished(ModelPublic m)
        {
            //获取全部信息(未发布版)
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            DataTable dt = null;

            try
            {
                if (m.UserType == "0")
                    sql = "select Id,Type,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Service where NavId='" + m.NavId + "' and Deleted='false' and Release='False' order by DateTime desc";
                if (m.UserType == "1")
                    sql = "select Id,Type,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Service where NavId='" + m.NavId + "' and Deleted='false' and Release='False' and Remarks='stu' order by DateTime desc";


                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//获取全部信息(未发布版)

        public DataTable Service_Inquire_Single(ModelPublic m)
        {
            //查询单条信息
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            DataTable dt = null;
            try
            {
                sql = "SELECT Id,NavId,Type,Adress,Title,_Content,CONVERT(varchar(10), DateTime,120) as DateTime,Author,Number,Release,FileName,FileRoute from Service where Id='" + m.Id + "'";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt != null)
                    return dt;
            }
            catch (Exception)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//查询单条信息

        public DataTable Service_Search_All(ModelPublic m)
        {
            //检索信息(发布版)
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            DataTable dt = null;

            try
            {
                if (m.UserType == "0")
                    sql = "SELECT Id,Type,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Service where (Title LIKE '%" + m.Conditions + "%' or DateTime like '%" + m.Conditions + "%' or Author like '%" + m.Conditions + "%') and NavId='" + m.NavId + "' and Deleted='false' and Release='true' order by DateTime desc";
                if (m.UserType == "1")
                    sql = "SELECT Id,Type,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Service where (Title LIKE '%" + m.Conditions + "%' or DateTime like '%" + m.Conditions + "%' or Author like '%" + m.Conditions + "%') and NavId='" + m.NavId + "' and Deleted='false' and Release='false' and Remarks='stu' order by DateTime desc";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//检索信息(发布版)

        public DataTable Service_Search_All_Unpublished(ModelPublic m)
        {
            //检索信息(未发布版)
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            DataTable dt = null;

            try
            {
                if (m.UserType == "0")
                    sql = "SELECT Id,Type,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Service where (Title LIKE '%" + m.Conditions + "%' or DateTime like '%" + m.Conditions + "%' or Author like '%" + m.Conditions + "%') and NavId='" + m.NavId + "' and Deleted='false' and Release='false' order by DateTime desc";
                if (m.UserType == "1")
                    sql = "SELECT Id,Type,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Service where (Title LIKE '%" + m.Conditions + "%' or DateTime like '%" + m.Conditions + "%' or Author like '%" + m.Conditions + "%') and NavId='" + m.NavId + "' and Deleted='false' and Release='false' and Remarks='stu' order by DateTime desc";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//检索信息(未发布版)

        public bool Service_Delete(ModelPublic m)
        {
            //信息删除
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            try
            {
                sql = "update Service set Deleted='true' where Id='" + m.Id + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return true;
                }
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//信息删除

        public bool Service_Modify(ModelPublic m)
        {
            //信息修改
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            try
            {
                sql = "update Service set Type="+m.Type+",Adress="+m.Adress+", Title=" + m.Title + ",DateTime=" + m.DateTime + ",Author=" + m.Author + ",_Content=" + m.Content + ",FileName=" + m.FileName + ",FileRoute=" + m.FileRoute + ",Release=" + m.Release + " where Id=" + m.Id + "";
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return true;
                }
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//信息修改

        public bool Browse_Frequency(ModelPublic m)
        {
            //阅读次数增加
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            try
            {
                sql = "update Service set Number=Number+1 where Id='" + m.Id + "'";

                cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//阅读次数增加
    }
}
